![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Shared Session In the multithreaded server configuration, the session information is also stored in the shared pool. This information includes the private SQL areas as well as sort areas. It is important to make sure that you do not run out of space in the shared pool for this information. To determine whether you should increase space for these shared sessions, you can extract the sum of memory allocated for all sessions and the maximum amount of memory allocated for sessions from the dynamic performance table V$SESSTAT. The information in this table is split into the memory used by the UGA (User Global Area) and that used by the PGA (Program Global Area). To retrieve this information, use a query such as the one in Listing 9.1. Listing 9.1 Retrieving Information from V$SESSTAT select sid, value "Session UGA Memory" from v$sesstat,v$statname WHERE name = 'session uga memory' AND v$sesstat.statistic# = v$statname.statistic#; select SUM(value) "Sum of Session UGA Memory" from v$sesstat,v$statname WHERE name = 'session uga memory' AND v$sesstat.statistic# = v$statname.statistic#; select sid, value "Session PGA Memory" from v$sesstat,v$statname WHERE name = 'session pga memory' AND v$sesstat.statistic# = v$statname.statistic#; select SUM(value) "Sum of Session PGA Memory" from v$sesstat,v$statname WHERE name = 'session pga memory' AND v$sesstat.statistic# = v$statname.statistic#; select sid, value "Session UGA Memory Max" from v$sesstat,v$statname WHERE name = 'session uga memory max' AND v$sesstat.statistic# = v$statname.statistic#; select SUM(value) "Sum of Session UGA Memory Max" from v$sesstat,v$statname WHERE name = 'session uga memory max' AND v$sesstat.statistic# = v$statname.statistic#; select sid, value "Session PGA Memory Max" from v$sesstat,v$statname WHERE name = 'session pga memory max' AND v$sesstat.statistic# = v$statname.statistic#; select SUM(value) "Sum of Session PGA Memory Max" from v$sesstat,v$statname WHERE name = 'session pga memory max' AND v$sesstat.statistic# = v$statname.statistic#; The result of this query looks something like Listing 9.2. Listing 9.2 The Results of the Query in Listing 9.1 SID Session UGA Memory ---------- ------------------ 1 7048 54 43928 5 14104 4 7048 3 7048 2 8956 6 rows selected. Sum of Session UGA Memory ------------------------ 83896 SID Session PGA Memory ---------- ------------------ 1 34032 54 76592 5 51056 2 42544 4 42544 3 42544 6 rows selected. Sum of Session PGA Memory ------------------------ 289312 SID Session UGA Memory Max ---------- ---------------------- 1 7048 3 7048 5 18288 54 48168 4 7048 2 8956 6 rows selected. Sum of Session UGA Memory Max ---------------------------- 96556 SID Session PGA Memory Max ---------- ---------------------- 1 34032 2 42544 4 42544 54 76592 5 51056 3 42544 6 rows selected. Sum of Session PGA Memory Max ----------------------------- 289312 The SID represents the session ID, which shows you the value for each server session. Tuning the Buffer CacheProbably the most important Oracle cache in the system is the buffer cache. The buffer cache makes up the majority of the Oracle SGA and is used for every query and update in the system. Each time a data block is read, it is copied into the buffer cache. Each time a modification is made, it is done in the buffer cache. Remember that each server process accesses the buffer cache directly. In a read operation, the server process first checks to see whether the requested data is already in the SGA. If it is, the data is accessed directly from the SGA. If the data is not already in the SGA, the server process copies the data from the data file into the SGA where it will be accessed. In an update operation, the server process modifies the data block buffer(s) in the SGA only. It is up to the DBWR to write these dirty buffers out to disk. With the exception of the CKPT process, only the DBWR writes to the data files. Because the buffer cache is accessed so frequently, it is important that the buffer cache has sufficient size to get a good cache-hit rate. The statistics for the buffer cache are kept in the dynamic performance table V$SYSSTAT. The important columns to view in this table are as follows:
To see how well the block buffer cache is doing, use this query: SQL> SELECT name, value 2 FROM v$sysstat 3 WHERE name IN ('db block gets', 'consistent gets', 'physical reads'); NAME VALUE ---------------------------------------------------------------- -------- db block gets 155 consistent gets 5293 physical reads 334 To calculate the cache hit ratio, use this formula: Cache Hit Ratio = 1 - ( PHYSICAL READS / ( DB BLOCK GETS + CONSISTENT GETS)) Cache Hit Ratio = 1 - (334 / ( 155 + 5293) ) = 1 -(334 / 5448) = 1 - 0.0613 = 0.938 This example shows a cache hit rate of 93.8 percent. If the cache-hit rate is lower than 70 or 80 percent, you may need to increase the database buffer cache to improve performance. The buffer cache can be increased by tuning the Oracle initialization parameter DB_BLOCK_BUFFERS.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |